package com.example.skycode5.dao;

import com.example.skycode5.Bean.NewsBean;
import com.example.skycode5.util.DBConnection;
import com.example.skycode5.util.Time;

import java.util.Date;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class NewsDao {
    DBConnection DBConn = new DBConnection();
    public List<NewsBean> getList(int page){
        Connection conn = DBConn.getConn();
        String sql = "select * from news";
        List<NewsBean> list = new ArrayList<NewsBean>();
        list = null;
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()){
                NewsBean newsBean = new NewsBean();
                newsBean.setNewsid(rs.getInt("newsid"));
                newsBean.setNewstitle(rs.getString("newsTitle"));
                newsBean.setNewscontent(rs.getString("newsContent"));
                newsBean.setNewstime(rs.getString("newsTime"));
                newsBean.setNewsname(rs.getString("AdminName"));
                list.add(newsBean);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    /*

     */
    public int addNews(NewsBean news){
        Connection conn = DBConn.getConn();
        String sql = "insert into news values(null,?,?,?,?)";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1, news.getNewstitle());
            pmst.setString(2, news.getNewscontent());
            pmst.setString(3, (new Date()).toString());
            pmst.setString(4, news.getNewsname());
            return pmst.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public int appdate(NewsBean news){
        Connection conn = DBConn.getConn();
        String sql = "update news set NewsTitle=?,NewsContent=?,NewsTime=? AdminName=? where Newsid=?";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1, news.getNewstitle());
            pmst.setString(2, news.getNewscontent());
            pmst.setString(3, (Time.getDate()));
            pmst.setString(4, news.getNewsname());
            pmst.setInt(5, news.getNewsid());
            return pmst.executeUpdate(sql);
       } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public int delete(int newsID){
        Connection conn = DBConn.getConn();
        try {
            String sql = "delete from news where NewsID=?";
            PreparedStatement pmst = conn.prepareStatement(sql);
             pmst.setInt(1,newsID);
             return pmst.executeUpdate(sql);
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 统计新闻总条数
     * @return
     */
    public  int count(){
        Connection conn = DBConn.getConn();
        try {
            String sql = "select count(*) from news";
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            if(rs.next()){
                return rs.getInt(1);//cout查询返回一行数据 第一行第一列就是总条数
            }
        }catch (SQLException e ){
            e.printStackTrace();
        }
        return 0;
    }

    public int update(NewsBean newsBean) {
        return appdate(newsBean);
    }

    public int add(NewsBean newsBean) {
        return appdate(newsBean);
    }

    public NewsBean getNewsByID(int id) {
        try {
            String sql = "select * from news where NewsID=?";
            NewsBean newsBean = new NewsBean();
            Connection conn = DBConn.getConn();
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1,id);
            ResultSet rs = pmst.executeQuery();
            if (rs.next()){
                newsBean.setNewsid(rs.getInt(1));
                newsBean.setNewstitle(rs.getString(2));
                newsBean.setNewscontent(rs.getString(3));
                newsBean.setNewstime(rs.getString(4));
                newsBean.setAdminName(rs.getString(5));
                return newsBean;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
